package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.core.tool.CObjectTool;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.user.bean.UserSecGroup;
import com.fzrskj.cloud.free.user.bean.UserSecGroupView;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserSecGroupDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserSecGroupDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        db.closeConnection();
    }

    //to add new record to database by bean
    public int addUserSecGroup(UserSecGroup obj) {
        //代理商数据隔离
        obj.setAgentId(CloudContextUtils.getAgentId());
        String sql = "insert into user_secgroup(sgId,sgName,sgSysName,sgDesc,hostType," +
                "createTime,userId,userName,regionId,regionName," +
                "zoneId,zoneName,isSystem,agentId)" +
                "values(?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?);";
        Object[] prm = {obj.getSgId(), obj.getSgName(), obj.getSgSysName(), obj.getSgDesc(), obj.getHostType(),
                obj.getCreateTime(), obj.getUserId(), obj.getUserName(), obj.getRegionId(), obj.getRegionName(),
                obj.getZoneId(), obj.getZoneName(), obj.getIsSystem(), obj.getAgentId()};
        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to add new record to database by map
    public int addUserSecGroup(Map<String, Object> values) {
        //代理商数据隔离
        values.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.insertRecord("user_secgroup", values);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by id
    public int delUserSecGroup(int id) {
        String sql = "delete from user_secgroup where id=?";
        Object[] params = {id};

        //代理商数据隔离
        sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        int ret = db.executeSql(sql, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by cdt
    public int delUserSecGroup(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.deleteRecord("user_secgroup", cdt);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to modify table record by values and condition
    public int modUserSecGroup(Map<String, Object> values, Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.modifyRecord("user_secgroup", values, cdt);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    //to query user_secgroup item for detail
    public UserSecGroup queryUserSecGroup(int id) {
        String sql = "select * from user_secgroup where id=?";
        Object[] params = {id};

        //代理商数据隔离
        sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        UserSecGroup ret = db.queryForBean(sql, UserSecGroup.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询安全组列表
     *
     * @param cdt
     * @param page
     * @param pagesize
     * @return
     */
    public List<UserSecGroupView> queryUserSecGroupList(Map<String, Object> cdt, int page, int pagesize) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        StringBuffer sb = new StringBuffer("select *,");
        sb.append("(select count(*) from user_secgroup_host ush,user_service us where ush.instanceId=us.instanceId and ush.sgId=usg.sgId) as instanceNum");
        sb.append(" from user_secgroup usg where 1=1 ");

        List<Object> list = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("sgName")) {
                sb.append(" and sgName like ? ");
                list.add("%" + c.getValue() + "%");
            } else if (c.getKey().equals("sgDesc")) {
                sb.append(" and sgDesc like ? ");
                list.add("%" + c.getValue() + "%");
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }
        sb.append(" order by createTime asc");
        if ((page > 0) && (pagesize > 0)) {
            sb.append(String.format(" limit %d,%d", (page - 1) * pagesize, pagesize));
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserSecGroupView> ret = db.queryForBeans(sb.toString(), UserSecGroupView.class, params);
        this.setErrCode(db.getErrCode());
        return ret;
    }

    /**
     * 统计安全组
     *
     * @param cdt
     * @return
     */
    public int queryUserSecGroupCount(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select count(id) from user_secgroup where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("sgName")) {
                sb.append(" and sgName like ? ");
                list.add("%" + c.getValue() + "%");
            } else if (c.getKey().equals("sgDesc")) {
                sb.append(" and sgDesc like ? ");
                list.add("%" + c.getValue() + "%");
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long ret = (Long) db.queryFieldValue(sb.toString(), Long.class, params, 1);
        this.setErrCode(db.getErrCode());
        return ret.intValue();
    }

    /**
     * 查询安全组
     *
     * @param cdt
     * @return
     */
    public UserSecGroup queryUserSecGroup(Map<String, Object> cdt) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        StringBuilder sb = new StringBuilder("select * from user_secgroup where 1=1 ");

        List<Object> list = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            sb.append(" and " + c.getKey() + "=?");
            list.add(c.getValue());
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        UserSecGroup ret = db.queryForBean(sb.toString(), UserSecGroup.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }
}